package com.zrrd.javaWeb.dao;

import com.zrrd.javaWeb.bean.Tea;
import com.zrrd.javaWeb.bean.Tea;
import com.zrrd.javaWeb.utils.DBUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author WanJl
 * @version 1.0
 * @title TeaDao
 * @description
 * @create 2024/1/22 9:58
 */
public class TeaDao {

    /**
     * 查询所有用户
     * @return
     */
    public List<Tea> queryAll(){
        Connection conn = DBUtils.getConn();
        PreparedStatement pstmt=null;
        ResultSet re=null;
        ArrayList<Tea> list=null;
        try {
            //3、编写SQL语句
            String sql="SELECT * FROM jms2301_db01.tea";
            //4、获取PreparedStatement对象，进行预编译SQL
            pstmt = conn.prepareStatement(sql);
            //5、执行SQL语句，获取查询结果
            re = pstmt.executeQuery();
            //6、处理查询结果
            list = new ArrayList<>();
            while (re.next()){
                Integer id = re.getInt(1);
                String teaName = re.getString(2);
                Integer teaTypeId = re.getInt(3);
                Integer oredered = re.getInt(4);
                String description = re.getString(5);
                Integer status = re.getInt(6);
                list.add(new Tea(id,teaName,teaTypeId,oredered,description,status));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                re.close();
                pstmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return list;
    }

    /**
     * 根据id查询
     * @param id
     * @return
     */
    public Tea queryOneById(Integer id){
        //1、获取连接
        Connection conn = DBUtils.getConn();
        //2、编写SQL语句
        String sql="SELECT * FROM jms2301_db01.tea WHERE id = ?";
        Tea tea=null;
        PreparedStatement pstmt=null;
        ResultSet re=null;
        try {
            //3、获取PreparedStatement
            pstmt = conn.prepareStatement(sql);
            //4、传递参数
            pstmt.setInt(1,id);
            //5、执行SQL语句，并获取执行结果
            re = pstmt.executeQuery();
            //6、处理执行结果
            while (re.next()){
                id = re.getInt(1);
                String teaName = re.getString(2);
                Integer teaTypeId = re.getInt(3);
                Integer oredered = re.getInt(4);
                String description = re.getString(5);
                Integer status = re.getInt(6);
                tea=new Tea(id,teaName,teaTypeId,oredered,description,status);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                re.close();
                pstmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return tea;
    }

    /**
     * 插入
     * @param tea
     * @return
     */
    public int insert(Tea tea){
        Connection conn=DBUtils.getConn();
        PreparedStatement pstmt =null;
        String sql="INSERT INTO jms2301_db01.tea (teaName, teaTypeId, ordered, description, status) VALUES (?,?,?,?,?)";
        int count =0;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,tea.getTeaName());
            pstmt.setInt(2,tea.getTeaTypeId());
            pstmt.setInt(3,tea.getOrdered());
            pstmt.setString(4,tea.getDescription());
            pstmt.setInt(5,tea.getStatus());
            count = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {;
                pstmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return count;
    }
    /**
     * 修改用户的方法
     * @param tea
     * @return
     */
    public int update(Tea tea){
        Connection conn=DBUtils.getConn();
        PreparedStatement pstmt =null;
        String sql="UPDATE jms2301_db01.tea SET teaname=?,teaTypeId=?,ordered=?,description=?,status=? WHERE id=?";
        int count =0;
        try {
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1,tea.getTeaName());
            pstmt.setInt(2,tea.getTeaTypeId());
            pstmt.setInt(3,tea.getOrdered());
            pstmt.setString(4,tea.getDescription());
            pstmt.setInt(5,tea.getStatus());
            pstmt.setInt(6,tea.getId());
            count=pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {;
                pstmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return count;
    }

    /**
     * 根据id删除用户的方法
     * @param id
     * @return
     */
    public int delete(Integer id){
        Connection conn=DBUtils.getConn();
        PreparedStatement pstmt =null;
        String sql="DELETE FROM jms2301_db01.tea WHERE id=?";
        int count =0;
        try {
            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,id);
            count=pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                pstmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return count;
    }
}
